using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;

namespace DataAccessLayer
{
    public class User
    {
        private string name;
        private string email;
        private string password;
        private string dob;
        private string gender;
        private string interset;
        private string secques;
        private string secans;
        private string connstring;
        
        public User(string connstring)
        {
            this.connstring = connstring;
        }
        public string NAME
        {
            get { return name; }
            set { name = value; }
        }
        public string EMAIL
        {
            get { return email; }
            set { email = value; }
        }
        public string PASSWORD
        {
            get { return password; }
            set { password = value; }
        }
        public string DOB
        {
            get { return dob; }
            set { dob = value; }
        }
        public string GENDER
        {
            get { return gender; }
            set { gender = value; }
        }
        public string INTEREST
        {
            get { return interset; }
            set { interset = value; }
        }
        public string SECQ
        {
            get { return secques; }
            set { secques = value; }
        }
        public string SECA
        {
            get { return secans; }
            set { secans = value; }
        }
        public void funcRegistration()
        {
            SqlConnection con = new SqlConnection(connstring);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_user_registration";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Name", name));
            cmd.Parameters.Add(new SqlParameter("@Email", email));
            cmd.Parameters.Add(new SqlParameter("@Password", password));
            cmd.Parameters.Add(new SqlParameter("@Dob", dob));
            cmd.Parameters.Add(new SqlParameter("@Gender",gender));
            cmd.Parameters.Add(new SqlParameter("@Interest", interset));
            cmd.Parameters.Add(new SqlParameter("@SecQues", secques));
            cmd.Parameters.Add(new SqlParameter("@SecAns", secans));
            cmd.ExecuteNonQuery();
            con.Close();
        }
        public int funcVerification(string email)
        {
            int k = 0;
            
            SqlConnection objCon = new SqlConnection(connstring);
            objCon.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Users", objCon);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds);
            int i = ds.Tables[0].Rows.Count;

            for (int j = 0; j < i; j++)
            {
                if (email == ds.Tables[0].Rows[j]["Email"].ToString())
                {
                    k = 1;
                    break;
                }
            }
            return k;
        }
        public bool login()
        {
            bool k = false;
            SqlConnection con = new SqlConnection(connstring);
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Users ", con);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds);
            int i = ds.Tables[0].Rows.Count;
            for (int j = 0; j < i; j++)
            {
                if (email == ds.Tables[0].Rows[j]["Email"].ToString() && password == ds.Tables[0].Rows[j]["Password"].ToString())
                {
                    k = true;
                }
            }
            return k;
        }
        public DataTable Load()
        {
            SqlDataReader s1 = null;
            SqlConnection con = new SqlConnection(connstring);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_loading_user";
            cmd.CommandType = CommandType.StoredProcedure;
            s1 = cmd.ExecuteReader();
            DataTable dtDisplay = new DataTable();
            dtDisplay.Load(s1);
            con.Close();
            return dtDisplay;
        }
        public DataTable UserProfile(string uid)
        {
            SqlDataReader s1 = null;
            SqlConnection con = new SqlConnection(connstring);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_user_profile";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@uid",uid));
            s1 = cmd.ExecuteReader();
            DataTable dtDisplay = new DataTable();
            dtDisplay.Load(s1);
            con.Close();
            return dtDisplay;
        }
        public DataTable LoadInterest()
        {
            SqlDataReader s1 = null;
            SqlConnection con = new SqlConnection(connstring);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "sp_load_interest";
            cmd.CommandType = CommandType.StoredProcedure;
            s1 = cmd.ExecuteReader();
            DataTable dtDisplay = new DataTable();
            dtDisplay.Load(s1);

            con.Close();
            return dtDisplay;
        }
    }
}
